package cn.edu.niit.dao;

import cn.edu.niit.db.JDBCUtil;
import cn.edu.niit.javabean.Book;
import cn.edu.niit.javabean.BookRecord;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    public List<Book> selectAll(int pageNum, int pageSize) {
        String sql = "select books.*, book_sort.name as sort " +
                "from books, book_sort where " +
                "books.sort_id=book_sort.id limit ?,?";

        List<Book> books = new ArrayList<>();
        try (ResultSet rs =
                     JDBCUtil.getInstance().executeQueryRS(sql,
                             new Object[]{(pageNum - 1) * pageSize,
                                     pageSize})) {

            while (rs.next()) {
                Book book = new Book(rs.getInt("id") + "",
                        rs.getString(
                                "name"),
                        rs.getString("author"),
                        rs.getString("sort"),
                        rs.getString("description"));
                books.add(book);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return books;
    }

    public List<Book> searchBooksByKeyword(String keyword,int pageNum, int pageSize) {
        String sql="select books.*, book_sort.name as sort " +
        "from books, book_sort where " +
                "books.sort_id=book_sort.id and books.name like ? limit ?,?";
        List<Book> books = new ArrayList<>();
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql, new Object[]{"%"+keyword+"%", (pageNum - 1) * pageSize,
                    pageSize});
            while (rs.next()) {
                Book book = new Book(rs.getInt("id") + "",
                        rs.getString(
                                "name"),
                        rs.getString("author"),
                        rs.getString("sort"),
                        rs.getString("description"));
                books.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return books;
    }

    public int count() {
        String sql = "select count(*) as countNum from books";
        try (ResultSet rs =
                     JDBCUtil.getInstance().executeQueryRS(sql,
                             new Object[]{})) {

            while (rs.next()) {
                int count = rs.getInt("countNum");
                return count;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int countSearch(String keyword) {
        String sql = "select count(*) as countNum from books where name like ?";
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql, new Object[]{"%"+keyword+"%"});
            while (rs.next()) {
                int count = rs.getInt("countNum");
                return count;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public boolean selectStore(String username, String bookId) {
        String sql1 = "select EXISTS( SELECT 1 from borrow_books " +
                "where book_id=? and card_id=?) as store";
        try (ResultSet rs =
                     JDBCUtil.getInstance().executeQueryRS(sql1,
                             new Object[]{
                                     bookId, username
                             });) {

            while (rs.next()) {
                return rs.getBoolean("store");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return false;
    }

    public int insertStoreBook(String username, String bookId) {
        String sql = "insert into borrow_books(book_id, card_id, " +
                "borrow_date) values(?,?,?)";
        int result = JDBCUtil.getInstance().executeUpdate(sql,
                new Object[]{
                        bookId, username,
                        new Date(System.currentTimeMillis())
                });
        return result;
    }

    public List<BookRecord> getMyBookRecord(String username,int pageNum,int pageSize) {
        String sql = "select b.name,b.author,bks.* from borrow_books bks,books b where card_id=? and b.id=bks.book_id and end_date is not null order by borrow_date desc limit ?,?";
        List<BookRecord> records = new ArrayList<>();
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql, new Object[]{username, (pageNum - 1) * pageSize,
                    pageSize});
            while (rs.next()) {
                BookRecord record = BookRecord.builder().author(rs.getString("author")).id(rs.getInt("id") + "").name(rs.getString("name"))
                        .book_id(rs.getString("book_id")).borrowDate(rs.getTimestamp("borrow_date")).returnDate(rs.getTimestamp("return_date"))
                        .endDate(rs.getTimestamp("end_date")).illegal(rs.getString("illegal")).managerId(rs.getString("manager_id")).build();
                records.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }

    public int getMyBookRecordCount(String username) {
        String sql = "select b.name,b.author,bks.* from borrow_books bks,books b where card_id=? and b.id=bks.book_id and end_date is not null order by borrow_date desc";
        List<BookRecord> records = new ArrayList<>();
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql, new Object[]{username});
            while (rs.next()) {
                BookRecord record = BookRecord.builder().author(rs.getString("author")).id(rs.getInt("id") + "")
                        .book_id(rs.getString("book_id")).borrowDate(rs.getTimestamp("borrow_date")).returnDate(rs.getTimestamp("return_date"))
                        .endDate(rs.getTimestamp("end_date")).illegal(rs.getString("illegal")).managerId(rs.getString("manager_id")).build();
                records.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records.size();
    }

    //收藏的书
    public List<BookRecord> getMyFavoriteBook(String username,int pageNum,int pageSize) {
        String sql = "select b.name,b.author,bks.* from borrow_books bks,books b where card_id=? and b.id=bks.book_id and end_date is null order by borrow_date desc limit ?,?";
        List<BookRecord> records = new ArrayList<>();
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql, new Object[]{username, (pageNum - 1) * pageSize,
                    pageSize});
            while (rs.next()) {
                BookRecord record = BookRecord.builder().author(rs.getString("author")).id(rs.getInt("id") + "").name(rs.getString("name"))
                        .book_id(rs.getString("book_id")).borrowDate(rs.getTimestamp("borrow_date")).returnDate(rs.getTimestamp("return_date"))
                        .endDate(rs.getTimestamp("end_date")).illegal(rs.getString("illegal")).managerId(rs.getString("manager_id")).build();
                records.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }

    public int getMyFavoriteBookCount(String username) {
        String sql = "select b.name,b.author,bks.* from borrow_books bks,books b where card_id=? and b.id=bks.book_id and end_date is null order by borrow_date desc";
        List<BookRecord> records = new ArrayList<>();
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql, new Object[]{username});
            while (rs.next()) {
                BookRecord record = BookRecord.builder().author(rs.getString("author")).id(rs.getInt("id") + "")
                        .book_id(rs.getString("book_id")).borrowDate(rs.getTimestamp("borrow_date")).returnDate(rs.getTimestamp("return_date"))
                        .endDate(rs.getTimestamp("end_date")).illegal(rs.getString("illegal")).managerId(rs.getString("manager_id")).build();
                records.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records.size();
    }

    public int delFavoriteBook(int id) {
        String sql = "delete from borrow_books where id=? and end_date is null";
        return JDBCUtil.getInstance().executeUpdate(sql, new Object[]{id});
    }

    //借书
    public int borrowBook(BookRecord bookRecord) {
        String sql = "insert into borrow_books (book_id, card_id, borrow_date,end_date,manager_id) values(?,?,?,?,?)";
        return JDBCUtil.getInstance().executeUpdate(sql, new Object[]{
                bookRecord.getBook_id(),
                bookRecord.getCard_id(),
                new Timestamp(System.currentTimeMillis()),
                new Timestamp(System.currentTimeMillis() + 2592000000L),
                "online"
        });
    }

    public List<BookRecord> getMyKeepBook(String username, int pageNum, int pageSize) {
        String sql = "select b.name,b.author,bks.* from borrow_books bks,books b where card_id=? and b.id=bks.book_id and end_date is not null and return_date is null order by borrow_date desc limit ?,?";
        List<BookRecord> records = new ArrayList<>();
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql, new Object[]{username, (pageNum - 1) * pageSize,
                    pageSize});
            while (rs.next()) {
                BookRecord record = BookRecord.builder().author(rs.getString("author")).id(rs.getInt("id") + "").name(rs.getString("name"))
                        .book_id(rs.getString("book_id")).borrowDate(rs.getTimestamp("borrow_date")).returnDate(rs.getTimestamp("return_date"))
                        .endDate(rs.getTimestamp("end_date")).illegal(rs.getString("illegal")).managerId(rs.getString("manager_id")).build();
                records.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }

    public int getMyKeepBookCount(String username) {
        String sql = "select count(0) as countNum from borrow_books bks,books b where card_id=? and b.id=bks.book_id and end_date is not null and return_date is null order by borrow_date desc";
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql, new Object[]{username});
            while (rs.next()) {
                 return rs.getInt("countNum");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
